package HuaWeiPractice;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class SqlReplaceAll_20210408 {
    public static void main(String[] args) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        c.setTime(new Date());
        c.add(Calendar.DATE, - 1);
        Date d = c.getTime();
        String month = df.format(d);
        String today=df.format(new Date())+ " 00:00:00";
        String sql2="select" +
                " '551' city_code," +
                "case when s.cause is null or s.cause='null' then s.remark else s.cause end cause," +// 调账备注
                "m.acct_cd acct_id," +// 账户id
                "s.prod_inst_id," +// 产品实例id
                "t.acc_num," +// 电话号码
                "s.order_type," +// 订单类型
                "s.adjust_type," +// 调账类型
                "s.adjust_item_id," +// 账单id
                "s.charge_before," +// 调账前金额
                "s.charge_late," +// 调账后金额
                "cast(s.charge_late as double)/100-cast(s.charge_before as double)/100 real_charge," +// 实际调账金额
                "s.status_date," +// 销账调账时间
                "s.BILLING_CYCLE_ID,"+
                "s.adjust_item_id " +
                " from bss.acct_item_adjust_551 s left join bss.PROD_INST_S_551 t on " +//销账表
                " t.prod_inst_id = s.prod_inst_id " +
                " left join bss.account_s_551 m on s.acct_id=m.acct_id and m.acct_id is not null and m.acct_cd<>'null'" +
                "  where substr(s.status_date,1,10)>='"+month+"' ";
        String sql3 = sql2;
        String[] area_code = new String[] {"552", "553", "554", "555", "556", "557", "558", "559", "550", "560", "561", "562", "563", "564", "566"};
        for (int i = 0; i <area_code.length ; i++) {
            sql3+=" union all "+sql2.replaceAll("551", area_code[i]);
        }
        //System.out.println(sql3);

        String sql0="select * from " +
                " (select " +
                "t.sheet_area," +
                "t.serial," +// 申请单号
                "t.con_number," +// 合同号
                "t.account_date," +// 账期
                "t.IS_DELL,"+//是否拆机
                "cast(t.account_money as double) account_money," +// 申请调账金额
                "t.deal_result," +// 调账结果
                "busi_num_r BUSI_NUM," +
                "t.create_time," +// itsm申请时间
                "row_number() over(partition by t.serial,busi_num_r,t.account_date,t.account_money order by t.insert_date desc) rank " +
                " from bss.cush_ah_adjust_interface t " +//itsm表
                " LATERAL VIEW explode(split(t.busi_num,',')) zqm AS busi_num_r " +//单行数据拆分成多行
//                " where t.deal_result<>'NULL' "
                " where t.deal_result in (1,2,4) "//1、自动调账成功；0、自动调账失败;2、复核调账成功；3、复核调账失败；4、一键退费
                + " ) m where m.rank=1 ";
        //System.out.println(sql0);

        String sql1="select t.sheet_area,t.serial,t.con_number,t.account_date,t.IS_DELL,t.account_money,t.deal_result,"
                + "t.BUSI_NUM,t.create_time "
                + " from sql0 t ,sql3 a "
                + " where t.serial=a.cause "
                + " group by  t.sheet_area,t.serial,t.con_number,t.account_date,t.IS_DELL,t.account_money,t.deal_result, t.BUSI_NUM,t.create_time ";
        System.out.println(sql1);
    }
}
